package com.pdwbdn.db;

import com.pdwbdn.utils.DbConnUtil;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.nio.charset.StandardCharsets;
import java.sql.*;

public class AccountsDao {
    public static LoginResult loginByPassword(String username, String password) {
        if (username == null || username.isEmpty() || password == null || password.isEmpty()) {
            return new LoginResult(false, "用户名或密码为空", -1L);
        }
        Connection connection = null;
        try {
            connection = DbConnUtil.getConn("d_taiwan");
            if (connection == null) {
                return new LoginResult(false, "连接失败", -1L);
            }
            QueryRunner runner = new QueryRunner();
            String sql = "SELECT * FROM d_taiwan.accounts WHERE accountname=?";
            ResultSetHandler<AccountsBean> handler = new BeanHandler<>(AccountsBean.class);
            AccountsBean accountsBean = runner.query(connection, sql, handler, username);
            connection.commit();
            if (accountsBean == null) {
                return new LoginResult(false, "帐号不存在", -1L);
            }
            String passwordMd5 = DigestUtils.md5Hex(password.getBytes(StandardCharsets.UTF_8));
            if (!passwordMd5.equals(accountsBean.getPassword())) {
                return new LoginResult(false, "密码错误", -1L);
            }
            return new LoginResult(true, "登录成功", accountsBean.getUID());
        } catch (Throwable t) {
            return new LoginResult(false, t.toString(), -1L);
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

    public static RegResult registerAccount(String username, String password, String qq) {
        if (username == null || username.isEmpty() ||
                password == null || password.isEmpty() ||
                qq == null || qq.isEmpty()) {
            return new RegResult(false, "填写的信息为空");
        }
        Connection connection = null;
        try {
            connection = DbConnUtil.getConn("d_taiwan");
            if (connection == null) {
                return new RegResult(false, "连接失败");
            }
            QueryRunner runner = new QueryRunner();
            String sqlSelect = "SELECT * FROM d_taiwan.accounts WHERE accountname=?";
            ResultSetHandler<AccountsBean> handler = new BeanHandler<>(AccountsBean.class);
            AccountsBean accountsBean = runner.query(connection, sqlSelect, handler, username);
            if (accountsBean != null) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "帐号已存在");
            }
            String sqlInsert = "INSERT INTO d_taiwan.accounts(accountname,password,qq,VIP) VALUES (?,?,?,'')";
            String passwordMd5 = DigestUtils.md5Hex(password.getBytes(StandardCharsets.UTF_8));
            int inserts = runner.update(connection, sqlInsert, username, passwordMd5, qq);
            if (inserts != 1) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "注册帐号失败");
            }
            accountsBean = runner.query(connection, sqlSelect, handler, username);
            if (accountsBean == null) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "获取帐号失败");
            }
            long uid = accountsBean.getUID();
            String sqlInsert1 = "INSERT INTO d_taiwan.member_info(m_id,user_id) VALUES (?,?)";
            int inserts1 = runner.update(connection, sqlInsert1, uid, uid);
            String sqlInsert2 = "INSERT INTO d_taiwan.member_white_account(m_id) VALUES (?)";
            int inserts2 = runner.update(connection, sqlInsert2, uid);
            String sqlInsert3 = "INSERT INTO taiwan_login.member_login(m_id) VALUES (?)";
            int inserts3 = runner.update(connection, sqlInsert3, uid);
            String sqlInsert4 = "INSERT INTO taiwan_cain_2nd.member_avatar_coin(m_id) VALUES (?)";
            int inserts4 = runner.update(connection, sqlInsert4, uid);
            String sqlInsert5 = "INSERT INTO taiwan_billing.cash_cera(account,cera,mod_tran,mod_date,reg_date) VALUES (?,1000,0,NOW(),NOW())";
            int inserts5 = runner.update(connection, sqlInsert5, uid);
            String sqlInsert6 = "INSERT INTO taiwan_billing.cash_cera_point(account,cera_point,mod_date,reg_date) VALUES (?,0,NOW(),NOW())";
            int inserts6 = runner.update(connection, sqlInsert6, uid);
            if (inserts1 + inserts2 + inserts3 + inserts4 + inserts5 + inserts6 != 6) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "注册帐号信息失败");
            }
            connection.commit();
            return new RegResult(true, "注册帐号成功");
        } catch (Throwable t) {
            DbUtils.rollbackQuietly(connection);
            return new RegResult(false, t.toString());
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

    public static RegResult modifyPassword(String username, String oldPwd, String newPwd) {
        if (username == null || username.isEmpty() ||
                oldPwd == null || oldPwd.isEmpty() ||
                newPwd == null || newPwd.isEmpty()) {
            return new RegResult(false, "填写的信息为空");
        }
        Connection connection = null;
        try {
            connection = DbConnUtil.getConn("d_taiwan");
            if (connection == null) {
                return new RegResult(false, "连接失败");
            }
            QueryRunner runner = new QueryRunner();
            String sqlSelect = "SELECT * FROM d_taiwan.accounts WHERE accountname=?";
            ResultSetHandler<AccountsBean> handler = new BeanHandler<>(AccountsBean.class);
            AccountsBean accountsBean = runner.query(connection, sqlSelect, handler, username);
            if (accountsBean == null) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "帐号不存在");
            }
            String oldPwdMd5 = DigestUtils.md5Hex(oldPwd.getBytes(StandardCharsets.UTF_8));
            if (!oldPwdMd5.equals(accountsBean.getPassword())) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "旧密码错误");
            }
            String sqlUpdate = "UPDATE d_taiwan.accounts SET password=? WHERE UID=?";
            String newPwdMd5 = DigestUtils.md5Hex(newPwd.getBytes(StandardCharsets.UTF_8));
            int updates = runner.update(connection, sqlUpdate, newPwdMd5, accountsBean.getUID());
            if (updates != 1) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "修改密码失败");
            }
            connection.commit();
            return new RegResult(true, "修改密码成功");
        } catch (Throwable t) {
            DbUtils.rollbackQuietly(connection);
            return new RegResult(false, t.toString());
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

    public static RegResult resetPassword(String username, String qq, String password) {
        if (username == null || username.isEmpty() ||
                qq == null || qq.isEmpty() ||
                password == null || password.isEmpty()) {
            return new RegResult(false, "填写的信息为空");
        }
        Connection connection = null;
        try {
            connection = DbConnUtil.getConn("d_taiwan");
            if (connection == null) {
                return new RegResult(false, "连接失败");
            }
            QueryRunner runner = new QueryRunner();
            String sqlSelect = "SELECT * FROM d_taiwan.accounts WHERE accountname=?";
            ResultSetHandler<AccountsBean> handler = new BeanHandler<>(AccountsBean.class);
            AccountsBean accountsBean = runner.query(connection, sqlSelect, handler, username);
            if (accountsBean == null) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "帐号不存在");
            }
            if (!qq.equals(accountsBean.getQq())) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "QQ号错误");
            }
            String sqlUpdate = "UPDATE d_taiwan.accounts SET password=? WHERE UID=?";
            String passwordMd5 = DigestUtils.md5Hex(password.getBytes(StandardCharsets.UTF_8));
            int updates = runner.update(connection, sqlUpdate, passwordMd5, accountsBean.getUID());
            if (updates != 1) {
                DbUtils.rollbackQuietly(connection);
                return new RegResult(false, "重置密码失败");
            }
            connection.commit();
            return new RegResult(true, "重置密码成功");
        } catch (Throwable t) {
            DbUtils.rollbackQuietly(connection);
            return new RegResult(false, t.toString());
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }
}
